﻿#ifndef DATABASEMANAGER_H
#define DATABASEMANAGER_H

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QDebug>
#include <QString>
#include <QSqlTableModel>
#include <QTableView>
#include <QVariant>
#include <QPair>
#include <QList>
#include <QMessageBox>
#include <QSettings>
#include <QFile>

class DatabaseManager {
public:
    static DatabaseManager& instance();
    QSqlDatabase getDatabase();

    // Operations for Authentication table
    bool insertAuthentication(const QString& username, const QString& password, const QString& position);
    bool checkAuthentication(const QString& username, const QString& password, const QString& position);
    bool updateAuthentication(int id, const QString& username, const QString& password, const QString& position);
    bool deleteAuthentication(int id);
    QSqlTableModel* getAuthenticationModel();
    void cleanAuthentication();

    // Operations for PurchaseOrder table
    bool insertPurchaseOrder(const QString& orderMaterialType, int amount, const QString& acceptDate, const QString& orderStatus, int prepaid, const QString& clientName, const QString& clientTEL);
    QSqlTableModel* getPurchaseOrderModel();
    bool markPurchaseOrderAsCompleted(int id);
    QSqlTableModel* getPurchaseOrderById(int id);
    QList<QPair<QString, QString>> getPurchaseOrderDateAndCondition();
    int getLastPurchaseOrderId();
    void cleanPurchaseOrder();

    // Operations for Finance table
    bool insertFinanceRecord(int matchingId, const QString& recordType, const QString& recordDate, int amount, int totalIncome, int totalOutcome, int totalInterest);
    QSqlTableModel* getFinanceTableModel();
    QSqlTableModel* getFinanceRecordById(int id);
    QList<int> getFinanceLastTotal();
    QList<QList<QVariant>> getFinanceRecordsForLinePlot();
    void cleanFinance();

    // Operations for EnterRecord table
    bool insertEnterRecord(const QString& enterDate, const QString& enterMaterialType, int enterAmount, int unitPrice, int totalPrice);
    QSqlTableModel* getEnterRecordModel();
    QSqlTableModel* getEnterRecordById(int id);
    int getLastEnterRecordId();

    // Operations for Inventory table
    bool changeInventory(const QString& rawMaterialType, int amount);
    QSqlTableModel* getInventoryModel();
    QList<QPair<QString, int>> getInventoryForPiePlot();
    QSqlTableModel* getInventoryByType(const QString& rawMaterialType);

public:
    DatabaseManager();
    DatabaseManager(const QString& dbName); // construct sqlite db
    ~DatabaseManager();
    DatabaseManager(const DatabaseManager&) = delete;
    DatabaseManager& operator=(const DatabaseManager&) = delete;

    void initializeSqliteDatabase();
    void initializeMySQLDatabase();
    bool initDatabaseConnection();



    void saveDatabaseConfig(const QString& dbType,
                         const QString& host,
                         int port,
                         const QString& dbName,
                         const QString& user,
                         const QString& password
                         );
    void saveSqliteConfig(const QString& dbName);
    bool loadDatabaseConfig(QString& dbType);
    bool loadSqliteConfig(QString& dbName);
    bool loadMySQLConfig(QString& host, int& port, QString& dbName, QString& user, QString& password);

    // Helper functions to switch between connections
    bool connectToSqlite(const QString& dbName);
    bool connectToMySQL(const QString& hostName, //主机
                        int port,                //端口
                        const QString& dbName,   //数据库名称
                        const QString& username, //用户账号
                        const QString& password  //密码
                        );
    void closePrevConnection();
    QList<int> getTableRowCounts();


    QSqlDatabase db;
};

#endif // DATABASEMANAGER_H
